using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

using MM.Entities;

namespace MM.Business
{
    public class clsPHIEU_NHAP_HANG
    {
        public clsPHIEU_NHAP_HANG(string sConn)
        {
            SqlHelper.ConnectionString = sConn;
        }

        #region Private methods
        private PHIEU_NHAP_HANG GetDataRow(DataRow row)
        {
            PHIEU_NHAP_HANG objPHIEU_NHAP_HANG = new PHIEU_NHAP_HANG();
            try
            {
                if (row["MaPhieuNhapHang"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.MaPhieuNhapHang = row["MaPhieuNhapHang"].ToString();
            }
            catch { }
            try
            {
                if (row["NgayNhap"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.NgayNhap = Convert.ToDateTime((row["NgayNhap"]));
            }
            catch { }
            try
            {
                if (row["NgayExport"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.NgayExport = Convert.ToDateTime((row["NgayExport"]));
            }
            catch { }
            try
            {
                if (row["NguoiNhap"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.NguoiNhap = (row["NguoiNhap"].ToString());
            }
            catch { }
            try
            {
                if (row["NguoiExport"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.NguoiExport = (row["NguoiExport"].ToString());
            }
            catch { }
            try
            {
                if (row["MaPNHThamChieu"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.MaPNHThamChieu = row["MaPNHThamChieu"].ToString();
            }
            catch { }
            try
            {
                if (row["TinhTrang"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.TinhTrang = int.Parse(row["TinhTrang"].ToString());
            }
            catch { }
            try
            {
                if (row["POThamChieuSAP"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.POThamChieuSAP = (row["POThamChieuSAP"].ToString());
            }
            catch { }
            try
            {
                if (row["SoLanExport"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.SoLanExport = int.Parse(row["SoLanExport"].ToString());
            }
            catch { }
            try
            {
                if (row["PlantReferenceSAP"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.PlantReferenceSAP = (row["PlantReferenceSAP"].ToString());
            }
            catch { }
            try
            {
                if (row["GhiChu"] != DBNull.Value)
                    objPHIEU_NHAP_HANG.GhiChu = (row["GhiChu"].ToString());
            }
            catch { }
            

            return objPHIEU_NHAP_HANG;
        }
        private List<PHIEU_NHAP_HANG> GetPHIEU_NHAP_HANG(DataTable dt)
        {
            List<PHIEU_NHAP_HANG> lst = new List<PHIEU_NHAP_HANG>();
            PHIEU_NHAP_HANG obj;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj = this.GetDataRow(dt.Rows[i]);
                lst.Add(obj);
            }
            return lst;
        }
        #endregion Private Method

        #region Public methods
        public string InsertPHIEU_NHAP_HANG(PHIEU_NHAP_HANG obj, List<CHI_TIET_PNH> lstCT)
        {
            string spName = "";
            SqlParameter[] par;
            int iResult = 0;
            string maphieunhaphang = "";
            try
            {
                SqlHelper.BeginTran();
                spName = "usp_InsertPHIEU_NHAP_HANG";
                par = new SqlParameter[12];
                par[0] = new SqlParameter("@MaPhieuNhapHang", SqlDbType.VarChar, 10);
                par[0].Direction = ParameterDirection.Output;
                par[1] = new SqlParameter("@NgayNhap", obj.NgayNhap);
                par[2] = new SqlParameter("@NguoiNhap", obj.NguoiNhap);
                par[3] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[4] = new SqlParameter("@SoLanExport", obj.SoLanExport);
                par[5] = new SqlParameter("@GRReferenceSAP", obj.GRReferenceSAP);
                par[6] = new SqlParameter("@MaPNHThamChieu", obj.MaPNHThamChieu);
                par[7] = new SqlParameter("@PlantReferenceSAP", obj.PlantReferenceSAP);
                par[8] = new SqlParameter("@NgayExport", null);
                par[9] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[10] = new SqlParameter("@POThamChieuSAP", obj.POThamChieuSAP);
                par[11] = new SqlParameter("@MaTau", obj.MaTau);

                iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                maphieunhaphang = par[0].Value.ToString();
                if (maphieunhaphang.Length != 10)
                {
                    SqlHelper.Tran.Rollback();
                    iResult = -1;
                }
                else
                {
                    int i = 0;
                    
                    for (i = 0; i < lstCT.Count; i++)
                    {
                       
                            spName = "usp_InsertCHI_TIET_PNH";
                            par = new SqlParameter[13];
                            par[0] = new SqlParameter("@MaChiTiet", lstCT[i].MaChiTiet);
                            par[1] = new SqlParameter("@SoLuong", lstCT[i].SoLuong);
                            par[2] = new SqlParameter("@MaChiTietPDH", lstCT[i].MaChiTietPDH);
                            par[3] = new SqlParameter("@MaThietBi", lstCT[i].MaVTPT);
                            par[4] = new SqlParameter("@TinhTrang", lstCT[i].TinhTrang);
                            par[5] = new SqlParameter("@MaPhieuNhapHang", maphieunhaphang);
                            par[6] = new SqlParameter("@MaPhieuHuyNhapHang", lstCT[i].MaPhieuHuyNhapHang);
                            par[7] = new SqlParameter("@MaKho", lstCT[i].MaKho);
                            par[8] = new SqlParameter("@NguoiTao", obj.NguoiNhap);
                            par[9] = new SqlParameter("@NgayTao", obj.NgayNhap);
                            par[10] = new SqlParameter("@MaTrangThai", lstCT[i].MaTrangThai);
                            par[11] = new SqlParameter("@AccAssignment", lstCT[i].AccAssignment);
                            par[12] = new SqlParameter("@MaTaiSan", lstCT[i].MaTaiSan);
                            

                            iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                            if (iResult <= 0)
                            {
                                SqlHelper.Tran.Rollback();
                                maphieunhaphang="";
                                break;
                            }
                        
                        
                    }
                    if (i == lstCT.Count && i > 0)
                    {
                        SqlHelper.CommitTran();
                    }
                    

                }



            }
            catch 
            {
                SqlHelper.Tran.Rollback();
                maphieunhaphang = "";

            }
            finally
            {
                SqlHelper.CloseConnection();
            }
            return maphieunhaphang;
        }

        public List<PHIEU_NHAP_HANG> usp_SelectPHIEU_NHAP_HANGsSearch(PHIEU_NHAP_HANG obj, List<VAT_TU_PHU_TUNG> lstVTPT, int DaDuyet)
        {
            string spName = "usp_SelectPHIEU_NHAP_HANGsSearch";
            DataTable dt = new DataTable();
            List<PHIEU_NHAP_HANG> lstpnh = new List<PHIEU_NHAP_HANG>();
            try
            {
                string temp = "";
                for (int i = 0; i < lstVTPT.Count; i++)
                {
                    if (i == 0)
                        temp = "(";
                    temp += "'" + lstVTPT[i]._maVTPT + "'";
                    if (i < lstVTPT.Count - 1)
                        temp += ",";
                    else
                        temp += ")";
                }
                SqlHelper.OpenConnection();
                SqlParameter[] par = new SqlParameter[9];
                par[0] = new SqlParameter("@MaPhieuNhapHang", obj.MaPhieuNhapHang == "" ? null : obj.MaPhieuNhapHang);
                if (obj.NgayNhapFrom.Year < 2012)
                    par[1] = new SqlParameter("@NgayTaoFrom", null);
                else
                    par[1] = new SqlParameter("@NgayTaoFrom", obj.NgayNhapFrom);
                  if (obj.NgayNhapTo.Year < 2012)
                      par[2] = new SqlParameter("@NgayTaoTo", null);
                  else
                      par[2] = new SqlParameter("@NgayTaoTo", obj.NgayNhapTo);
                par[3] = new SqlParameter("@NguoiTao", obj.NguoiNhap=="" ? null : obj.NguoiNhap);
                par[4] = new SqlParameter("@POThamChieuSAP", obj.POThamChieuSAP == "" ? null : obj.POThamChieuSAP);
                par[5] = new SqlParameter("@MaKho", obj.MaKho == "" ? null : obj.MaKho);
                par[6] = new SqlParameter("@MaVTPTs", temp == "" ? null : temp);

                if (DaDuyet == 0)// chua duyet
                    par[7] = new SqlParameter("@DaDuyet", false);
                else
                    if (DaDuyet == 1)// dc duyet
                        par[7] = new SqlParameter("@DaDuyet", true);
                    else
                        par[7] = new SqlParameter("@DaDuyet", null);
                par[8] = new SqlParameter("@TinhTrang", obj.TinhTrang);

                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lstpnh = this.GetPHIEU_NHAP_HANG(dt);

            }
            catch
            { 

            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }


            return lstpnh;
        }

        public List<PHIEU_NHAP_HANG> usp_SelectPHIEU_NHAP_HANG(string MaPhieuNhapHang)
        {
            string spName = "usp_SelectPHIEU_NHAP_HANG";
            DataTable dt = new DataTable();
            List<PHIEU_NHAP_HANG> lst = new List<PHIEU_NHAP_HANG>();
            try
            {
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaPhieuNhapHang", MaPhieuNhapHang);

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }

                lst = this.GetPHIEU_NHAP_HANG(dt);
            }
            catch 
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public List<PHIEU_NHAP_HANG> usp_SelectPHIEU_NHAP_HANG_MAX(string matau)
        {
            string spName = "select top 1 * from phieu_nhap_hang where maphieunhaphang like '%"+matau+"%' order by maphieunhaphang desc";
            DataTable dt = new DataTable();
            List<PHIEU_NHAP_HANG> lst = new List<PHIEU_NHAP_HANG>();
            try
            {
                SqlHelper.OpenConnection();
                
                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName,CommandType.Text);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, CommandType.Text);
                }

                lst = this.GetPHIEU_NHAP_HANG(dt);
            }
            catch 
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public int usp_UpdatePHIEU_NHAP_HANG_Duyet(PHIEU_NHAP_HANG obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_NHAP_HANG_Duyet";

            try
            {
                SqlParameter[] par = new SqlParameter[7];
                par[0] = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                par[0].Direction = ParameterDirection.ReturnValue;
                par[1] = new SqlParameter("@MaPhieuNhapHang", obj.MaPhieuNhapHang);
                par[2] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[3] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[4] = new SqlParameter("@NgayTao", obj.NgayNhap);
                par[5] = new SqlParameter("@NguoiTao", obj.NguoiNhap);
                par[6] = new SqlParameter("@GhiChu", obj.GhiChu);



                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

                iResult = int.Parse(par[0].Value.ToString());

            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_NHAP_HANG(PHIEU_NHAP_HANG obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_NHAP_HANG";

            try
            {
                SqlParameter[] par = new SqlParameter[11];
                                
                par[0] = new SqlParameter("@MaPhieuNhapHang", obj.MaPhieuNhapHang);
                par[1] = new SqlParameter("@NgayNhap", obj.NgayNhap);
                par[2] = new SqlParameter("@NguoiNhap", obj.NguoiNhap);
                par[3] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[4] = new SqlParameter("@SoLanExport", obj.SoLanExport);
                par[5] = new SqlParameter("@GRReferenceSAP", obj.GRReferenceSAP);
                par[6] = new SqlParameter("@MaPNHThamChieu", obj.MaPNHThamChieu);
                par[7] = new SqlParameter("@PlantReferenceSAP", obj.PlantReferenceSAP);

                par[8] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[9] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[10] = new SqlParameter("@POThamChieuSAP", obj.POThamChieuSAP);


                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

                

            }
            catch 
            {
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_NHAP_HANG_GoDuyet(PHIEU_NHAP_HANG obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_NHAP_HANG_GoDuyet";

            try
            {
                SqlParameter[] par = new SqlParameter[3];
                par[0] = new SqlParameter("@MaPhieuNhapHang", obj.MaPhieuNhapHang);                
                par[1] = new SqlParameter("@NgayTao", obj.NgayNhap);
                par[2] = new SqlParameter("@NguoiTao", obj.NguoiNhap);



                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch 
            {
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_NHAP_HANG_Xoa(PHIEU_NHAP_HANG obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_NHAP_HANG_Xoa";

            try
            {
                SqlParameter[] par = new SqlParameter[4];
                par[0] = new SqlParameter("@MaPhieuNhapHang", obj.MaPhieuNhapHang);
                par[1] = new SqlParameter("@NgayNhap", obj.NgayNhap);
                par[2] = new SqlParameter("@NguoiNhap", obj.NguoiNhap);
                par[3] = new SqlParameter("@TinhTrang", obj.TinhTrang);

                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch
            {
            }
            return iResult;
        }

        public void CsvExportPHIEU_NHAP_HANG(PHIEU_NHAP_HANG pNH, List<CHI_TIET_PNH> lst, string filepath)
        {
            StringBuilder sb = new StringBuilder();

            string d = "0"+pNH.NgayNhap.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            string m = "0" + pNH.NgayNhap.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            string y = pNH.NgayNhap.Year.ToString();

            sb.Append(pNH.MaPhieuNhapHang);
            sb.Append(";");
            sb.Append(pNH.POThamChieuSAP);
            sb.Append(";");
            sb.Append(d+"."+m+"."+y);
            sb.Append(";");
            sb.Append(pNH.GhiChu);
            if (pNH.TinhTrang == 0)
            {

                for (int i = 0; i < lst.Count; i++)
                {
                    if (lst[i].TinhTrang == 0)
                    {
                        sb.AppendLine();
                        if (pNH.POThamChieuSAP!="")
                            sb.Append(lst[i].POItem_No);
                        else
                            sb.Append("");
                        sb.Append(";");
                        sb.Append(lst[i].MaVTPT);
                        sb.Append(";");
                        sb.Append(lst[i].SoLuong);
                        sb.Append(";");
                        sb.Append(lst[i].oKHO._maKhoThamChieuSAP);
                        sb.Append(";");
                        sb.Append(lst[i].MaChiTiet);
                        sb.Append(";");
                        sb.Append(lst[i].oTRANG_THAI_VTPT.ValuationType);              
                                              
                    }
                }
            }

            File.WriteAllText(filepath, sb.ToString());
        }

        public void CsvExportPHIEU_NHAP_HANG_Cancelation(string ghichu, List<CHI_TIET_PNH> lst, string filepath)
        {
            StringBuilder sb = new StringBuilder();

            string d = "0" + lst[0].NgayHuyNhapHang.Value.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            string m = "0" + lst[0].NgayHuyNhapHang.Value.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            string y = lst[0].NgayHuyNhapHang.Value.Year.ToString();

            sb.Append(lst[0].MaPhieuHuyNhapHang);
            sb.Append(";");
            sb.Append(lst[0].MaPhieuNhapHang);
            sb.Append(";");
            sb.Append(d + "." + m + "." + y);
            sb.Append(";");
            sb.Append(ghichu);


            for (int i = 0; i < lst.Count; i++)
            {
                if (lst[i].TinhTrang == 0)
                {
                    sb.AppendLine();
                    sb.Append(lst[i].MaChiTiet);
                    sb.Append(";");
                    sb.Append(lst[i].MaVTPT);                   
                }
            }


            File.WriteAllText(filepath, sb.ToString());
        }

        #endregion
    }
}
